package itsm.isperp.module.controller;

import itsm.isperp.framework.core.context.ContextHolder;
import itsm.isperp.framework.web.request.JqGridColModel;
import itsm.isperp.framework.web.request.JqGridExcelExportRequest;
import itsm.isperp.framework.web.request.JqGridRequest;
import itsm.isperp.framework.web.response.view.ExcelView;
import itsm.isperp.framework.web.servlet.http.ParameterHttpServletRequestWrapper;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Type;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.sitemesh.webapp.contentfilter.HttpServletRequestFilterable;
import org.springframework.beans.factory.BeanFactoryUtils;
import org.springframework.data.domain.Page;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.method.HandlerMethod;
import org.springframework.web.servlet.HandlerMapping;
import org.springframework.web.servlet.ModelAndView;
import org.springframework.web.servlet.mvc.method.RequestMappingInfo;
import org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerMapping;

@Controller
@RequestMapping("exportExcel")
public class ExportExcelController {

	/**
	 * 导出到Excel操作
	 * 
	 * @param request
	 *            {@link JqGridRequest} jqGrid数据请求（查询条件、排序）
	 * @param exportOptions
	 *            {@link JqGridExcelExportRequest} 一个在基于jqGrid的导出请求
	 * @param req
	 *            {@link HttpServletRequest} http请求
	 * @return excel文件下载
	 * @throws SecurityException
	 * @throws NoSuchFieldException
	 * @throws ClassNotFoundException
	 */
	@RequestMapping(value = "", method = RequestMethod.GET)
	public ModelAndView doGet(JqGridRequest request, HttpServletRequest req) throws IllegalAccessException, IllegalArgumentException, InvocationTargetException, ClassNotFoundException, NoSuchFieldException, SecurityException {

		String url = null, tUrl = null;

		String exportOptions = req.getParameter("exportOptions");

		if (StringUtils.isEmpty(exportOptions))
			return new ModelAndView();

		String[] urlArr = exportOptions.split(";");

		if (urlArr.length != 2) {
			return new ModelAndView();
		}

		String[] columnNames = urlArr[0].replaceAll("'", "").split(",");

		url = urlArr[1];

		if (url.contains("?")) {

			String[] tUrlArr = url.split("\\?");
			tUrl = tUrlArr[0];
			// List<NameValuePair> ps = URLEncodedUtils.parse(tUrlArr[1],
			// Charset.forName("UTF-8"));

			String[] ps = tUrlArr[1].split("&");

			if (req instanceof HttpServletRequestFilterable) {
				HttpServletRequestFilterable hrFilter = (HttpServletRequestFilterable) req;
				if (hrFilter.getRequest() instanceof ParameterHttpServletRequestWrapper) {
					ParameterHttpServletRequestWrapper pw = (ParameterHttpServletRequestWrapper) hrFilter.getRequest();
					for (String nv : ps) {
						String[] nvs = nv.split("=");
						if (nvs != null && nvs.length == 2)
							pw.addParameter(nvs[0], nvs[1]);

					}
				}
			}
		} else {
			tUrl = url;
		}

		if (!tUrl.startsWith("/")) {
			tUrl = "/" + tUrl;
		}

		Map<String, HandlerMapping> map = BeanFactoryUtils.beansOfTypeIncludingAncestors(ContextHolder.getSpringBeanFactory(), HandlerMapping.class, true, false);

		Set<String> keys = map.keySet();

		for (String key : keys) {
			HandlerMapping m = map.get(key);

			if (m instanceof RequestMappingHandlerMapping) {

				RequestMappingHandlerMapping rm = (RequestMappingHandlerMapping) m;
				Map<RequestMappingInfo, HandlerMethod> ms = rm.getHandlerMethods();

				Set<RequestMappingInfo> rms = ms.keySet();
				for (RequestMappingInfo rmi : rms) {

					if (rmi.getPatternsCondition().getPatterns().contains(tUrl)) {

						HandlerMethod me = ms.get(rmi);

						Class<?> clazz = me.getBeanType();

						Object obj = ContextHolder.getSpringBean(clazz);
						request.setOper("excel");
						request.setRows(0);

						Type[] types = me.getMethod().getGenericParameterTypes();
						List<Object> ps = new LinkedList<>();
						for (Type t : types) {

							if (t.equals(JqGridRequest.class)) {
								ps.add(request);
							} else if (t.equals(HttpServletRequest.class)) {
								ps.add(req);
							}

						}

						Object result = me.getMethod().invoke(obj, ps.toArray());

						if (tUrl.indexOf("configuration/ci") > -1) {
							String[] selectColumns = request.getSelectColumns();
							String[] tempColumnNames = columnNames;
							List<String> listSelectColumns = new LinkedList<String>();
							List<String> listSelectColumnNames = new LinkedList<String>();
							for (String str : selectColumns) {
								if (str.indexOf("from_relation_count")<0 && str.indexOf("to_relation_count")<0) {
									listSelectColumns.add(str);
								}
							}
							for (String str : tempColumnNames) {
								if (!str.equals("被关联数") && !str.equals("关联资产") && str.indexOf("undefined")<0) {
									listSelectColumnNames.add(str);
								}
							}
							String[] result1 = {};
							String[] result2 = {};
							return excelExport(result, listSelectColumns.toArray(result1), listSelectColumnNames.toArray(result2));

						} else {
							return excelExport(result, request.getSelectColumns(), columnNames);
						}

					}
				}
			}

		}

		return null;
	}
	
	@RequestMapping(value = "exportCiExcel", method = RequestMethod.GET)
	public ModelAndView exportCiExcel(JqGridRequest request, HttpServletRequest req) throws IllegalAccessException, IllegalArgumentException, InvocationTargetException, ClassNotFoundException, NoSuchFieldException, SecurityException {

		String url = null, tUrl = null;

		String exportOptions = req.getParameter("exportOptions");

		if (StringUtils.isEmpty(exportOptions))
			return new ModelAndView();

		String[] urlArr = exportOptions.split(";");

		if (urlArr.length != 2) {
			return new ModelAndView();
		}

		String[] columnNames = urlArr[0].replaceAll("'", "").replaceAll("undefined,","").split(",");

		url = urlArr[1];

		if (url.contains("?")) {

			String[] tUrlArr = url.split("\\?");
			tUrl = tUrlArr[0];
			// List<NameValuePair> ps = URLEncodedUtils.parse(tUrlArr[1],
			// Charset.forName("UTF-8"));

			String[] ps = tUrlArr[1].split("&");

			if (req instanceof HttpServletRequestFilterable) {
				HttpServletRequestFilterable hrFilter = (HttpServletRequestFilterable) req;
				if (hrFilter.getRequest() instanceof ParameterHttpServletRequestWrapper) {
					ParameterHttpServletRequestWrapper pw = (ParameterHttpServletRequestWrapper) hrFilter
							.getRequest();
					for (String nv : ps) {
						String[] nvs = nv.split("=");
						if (nvs != null && nvs.length == 2)
							pw.addParameter(nvs[0], nvs[1]);

					}
				}
			}
		} else {
			tUrl = url;
		}

		if (!tUrl.startsWith("/")) {
			tUrl = "/" + tUrl;
		}

		Map<String, HandlerMapping> map = BeanFactoryUtils
				.beansOfTypeIncludingAncestors(
						ContextHolder.getSpringBeanFactory(),
						HandlerMapping.class, true, false);

		Set<String> keys = map.keySet();

		for (String key : keys) {
			HandlerMapping m = map.get(key);

			if (m instanceof RequestMappingHandlerMapping) {

				RequestMappingHandlerMapping rm = (RequestMappingHandlerMapping) m;
				Map<RequestMappingInfo, HandlerMethod> ms = rm
						.getHandlerMethods();

				Set<RequestMappingInfo> rms = ms.keySet();
				for (RequestMappingInfo rmi : rms) {

					if (rmi.getPatternsCondition().getPatterns().contains(tUrl)) {

						HandlerMethod me = ms.get(rmi);

						Class<?> clazz = me.getBeanType();

						Object obj = ContextHolder.getSpringBean(clazz);
						request.setOper("excel");
						request.setRows(0);

						Type[] types = me.getMethod()
								.getGenericParameterTypes();
						List<Object> ps = new LinkedList<>();
						for (Type t : types) {

							if (t.equals(JqGridRequest.class)) {
								ps.add(request);
							} else if (t.equals(HttpServletRequest.class)) {
								ps.add(req);
							}

						}

						Object result = me.getMethod()
								.invoke(obj, ps.toArray()); 
						String[] selectColumns = request.getSelectColumns();
						String[] tempColumnNames = columnNames;
						List<String> listSelectColumns = new LinkedList<String>();
						List<String> listSelectColumnNames = new LinkedList<String>();
						for (String str : selectColumns) {
							if (str.indexOf("from_relation_count")<0 && str.indexOf("to_relation_count")<0) {
								listSelectColumns.add(str);
							}
						}
						for (String str : tempColumnNames) {
							if (!str.equals("被关联数") && !str.equals("关联资产") && str.indexOf("undefined")<0) {
								listSelectColumnNames.add(str);
							}
						}
						String[] result1 = {};
						String[] result2 = {};
						columnNames=listSelectColumnNames.toArray(result2);
						String[] tSelectColumns=listSelectColumns.toArray(result1);
						List list0 = null;
						if (result instanceof List) {
							list0 = (List) result;
						} else if (result instanceof Page) {
							list0 = ((Page) result).getContent();
						}
						
						if (list0.size()==0)
							return null;
						
						String ids="";
						for(int i=0;i<list0.size();i++)
						{
							Map<String,Object> ob=(Map)list0.get(i);
						    String tid=ob.get("id").toString();
						    ids+="'"+tid+"',";
						}
						ids=ids.substring(0,ids.length()-1);
						//获取result的ids，重新获取Result
						String categoryId=req.getParameter("categoryId");
						//根据categoryId获取所有扩展属性字段
						JdbcTemplate jdbcTemplate = ContextHolder.getSpringBean("jdbcTemplate");
						String sql="SELECT a.name AS cn,a.conf_category_id as rcagegory_id FROM conf_category_property a JOIN conf_category b ON a.conf_category_id=b.id WHERE (SELECT c.treenode_path FROM conf_category c WHERE c.id='"+categoryId+"') LIKE CONCAT(CONCAT('%',a.conf_category_id),'%') ORDER BY b.treenode_path ASC";
						List<Map<String,Object>> list=jdbcTemplate.queryForList(sql);
						StringBuilder sb=new StringBuilder("SELECT a.*,t.*,ak.name  category_name FROM (SELECT k.*,0 AS ci_change_count,0 AS ci_incident_count,(SELECT COUNT(*) FROM conf_ci_relation WHERE from_id = k.id) AS from_relation_count,(SELECT COUNT(*) FROM conf_ci_relation WHERE to_id = k.id) AS to_relation_count FROM conf_ci k)a left join conf_category ak on a.conf_category_id=ak.id left join (select id as tid,");
						String[] tmpCns=new String[list.size()];
						String rCategoryId="";
						if(list.size()>0)
							rCategoryId=list.get(0).get("rcagegory_id").toString();
						for(int i=0;i<list.size();i++)
						{
							String tcn=list.get(i).get("cn").toString();
							tmpCns[i]=tcn;
							sb.append("GROUP_CONCAT(if(cname='"+tcn+"',cvalue,'') SEPARATOR '') '"+tcn+"',");
						}
						sb.deleteCharAt(sb.length()-1);
						sb.append(" from v_conf_category_property where ctreenodepath like '%"+rCategoryId+"%'  group by id)t ");
                        sb.append(" on a.id=t.tid where a.id in("+ids+")");
						String ts=sb.toString();
						List<Map<String,Object>> li=jdbcTemplate.queryForList(ts);
						List tnsl=new ArrayList(); 
						for(String s:tSelectColumns)
						{
							if(s.contains("p1t.p1") || s.contains("p2t.p2") || s.contains("p3t.p3") || s.contains("p4t.p4") || s.contains("p5t.p5") || s.contains("p6t.p6"))
							{
								continue;
							}
							tnsl.add(s);
						}
						int lsiz=tnsl.size();
						String[] tmpCns1=(String[])tnsl.toArray(new String[lsiz]);
						String[] newSelectColumns = (String[]) ArrayUtils.addAll(tmpCns1, tmpCns);
						
						String[] tmpColumnNames=new String[columnNames.length];
						for(int i=0;i<tmpColumnNames.length;i++)
						{
							tmpColumnNames[i]=columnNames[i];
						}
						String[] newcolumnNames=(String[]) ArrayUtils.addAll(tmpColumnNames, tmpCns);
						return excelExport(li, newSelectColumns,
								newcolumnNames);
					}
				}
			}
		}
		return null;
	}

	private ModelAndView excelExport(Object result, String[] columns, String[] columnNames) {

		int length = columns.length;
		List<JqGridColModel> models = new ArrayList<>();
		for (int i = 0; i < length; i++) {
			if (StringUtils.isNotEmpty(columnNames[i])) {
				JqGridColModel m = new JqGridColModel();
				m.setName(columns[i]);
				m.setLabel(columnNames[i]);
				models.add(m);
			}
		}

		Map<String, Object> model = new HashMap<String, Object>();
		model.put("list", result);
		model.put("columns", models);
		return new ModelAndView(new ExcelView(), model);

	}
}
